package com.yanyu.space.codegen.service.impl;

import cn.hutool.db.ds.simple.SimpleDataSource;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yanyu.space.codegen.bean.enums.CodegenRCode;
import com.yanyu.space.codegen.bean.po.DsSetting;
import com.yanyu.space.codegen.bean.vo.dssetting.DsSettingInfoVo;
import com.yanyu.space.codegen.bean.vo.dssetting.DsSettingSaveVo;
import com.yanyu.space.codegen.bean.vo.dssetting.DsSettingSearcheVo;
import com.yanyu.space.codegen.bean.vo.dssetting.TableInfoVo;
import com.yanyu.space.codegen.dao.DsSettingMapper;
import com.yanyu.space.codegen.service.IDsSettingService;
import com.yanyu.space.common.core.bean.db.PageEntity;
import com.yanyu.space.common.core.exception.ServiceException;
import com.yanyu.space.common.db.pagination.PageUtil;
import lombok.AllArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库配置 服务实现类
 *
 * @author yanyu
 * @date 2020/11/18
 */
@Service
@AllArgsConstructor
public class DsSettingServiceImpl extends ServiceImpl<DsSettingMapper, DsSetting> implements IDsSettingService {

    @Override
    public PageEntity<DsSettingInfoVo> listPage(DsSettingSearcheVo vo) {
        IPage<DsSetting> page = baseMapper.selectPage(PageUtil.getParam(),
                Wrappers.<DsSetting>lambdaQuery().like(StringUtils.isNoneBlank(vo.getName()), DsSetting::getName, vo.getName()).orderByDesc(DsSetting::getCreateTime));
        return PageUtil.getResult(page, DsSettingInfoVo.class);
    }

    @Override
    public void testDs(Long id) {
        Connection connection = null;
        try {
            DsSetting dsSetting = baseMapper.selectById(id);
            String password = dsSetting.getPassword();
            String dsUrl = StringUtils.join("jdbc:mysql://", dsSetting.getUrl(), "/", dsSetting.getName(), "?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf-8&useSSL=true");
            DataSource ds = new SimpleDataSource(dsUrl, dsSetting.getUsername(), password);
            connection = ds.getConnection();
        } catch (SQLException e) {
            throw new ServiceException(CodegenRCode.TESTDS_FAIL, e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                log.error("关闭数据库连接失败", e);
            }
        }
    }

    @Override
    public List<TableInfoVo> listDsTables(Long dsId) {
        // 1. 获取数据连接信息
        DsSetting dsSetting = baseMapper.selectById(dsId);
        if (dsSetting == null) {
            return new ArrayList<>();
        }

        Connection connection = null;
        Statement stmt = null;
        try {
            String password = dsSetting.getPassword();
            String dsUrl = StringUtils.join("jdbc:mysql://", dsSetting.getUrl(), "/", dsSetting.getName(), "?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf-8&useSSL=true");
            DataSource ds = new SimpleDataSource(dsUrl, dsSetting.getUsername(), password);
            connection = ds.getConnection();
            stmt = connection.createStatement();
            String sql = "select table_name tableName, engine, table_comment tableComment from information_schema.tables where table_schema = (select database())";
            ResultSet rs = stmt.executeQuery(sql);
            List<TableInfoVo> tableInfoVos = new ArrayList<>();
            while (rs.next()) {
                String tableName = rs.getString("tableName");
                String engine = rs.getString("engine");
                String tableComment = rs.getString("tableComment");
                TableInfoVo tableInfoVo = new TableInfoVo();
                tableInfoVo.setTableName(tableName);
                tableInfoVo.setEngine(engine);
                tableInfoVo.setTableComment(tableComment);
                tableInfoVos.add(tableInfoVo);
            }
            return tableInfoVos;
        } catch (SQLException e) {
            throw new ServiceException(CodegenRCode.TESTDS_FAIL, e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                log.error("关闭数据库连接失败", e);
            }
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void add(DsSettingSaveVo vo) {
        DsSetting dsSetting = new DsSetting();
        BeanUtils.copyProperties(vo, dsSetting);
        baseMapper.insert(dsSetting);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void update(DsSettingSaveVo vo) {
        DsSetting dsSetting = new DsSetting();
        BeanUtils.copyProperties(vo, dsSetting);
        baseMapper.updateById(dsSetting);
    }
}
